# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
import sys
from xlutils.copy import copy
import os

reload(sys)
sys.setdefaultencoding('utf8')
# sys.stdout = io.TextIOWrapper(sys.stdout.buffer,c='gb18030')

"""
author：zhangqing
function: 数据初始化对比：cvs1.0与cvs2.0 人物详情页职业生涯对比（公司全称、职位、职业生涯排序）
"""

def searchCvs1PersionManager(persionIdList):
    db = MySQLdb.connect("119.254.13.183", "amy_zhang", "ZHE0yeuWHAkqyUdw", "datacenter_online_20180609", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)

    cursor = db.cursor()

    print("persionIdList==",persionIdList)
    #循环获取1.0库中的persionId，找到符合条件id后去dc_manager表中获取排名第一的职业生涯信息
    for index in range(len(persionIdList)):
        persionId1 = persionIdList[index].get(u'persionId1')
        persionId2 = persionIdList[index].get(u'persionId2')
        sql = "SELECT personage.id ,personage.cn_name AS personName, manager.context_id, manager.context_type, " \
              "manager.title, manager.career_order AS person_order, manager.ext2 AS full_name, manager.ext1 AS beizhu  " \
              "from" \
              " dc_personage personage,dc_manager manager " \
              "WHERE personage.id = manager.personage_id AND personage.`status`='1' AND manager.context_type IN('2','3','4') AND personage.id='%s' " \
              "ORDER BY manager.personage_id,manager.career_order "%persionId1

        # sql = "SELECT personage.id ,personage.cn_name AS personName, manager.context_id, manager.context_type, " \
        #       "manager.title, manager.career_order AS person_order, manager.ext2 AS full_name, manager.ext1 AS beizhu  " \
        #       "from" \
        #       " dc_personage personage,dc_manager manager " \
        #       "WHERE personage.id = manager.personage_id AND personage.`status`='1' AND manager.context_type IN('2','3','4') AND personage.id='d6864a445c9111e79a23ac1667c194a1' " \
        #       "ORDER BY manager.personage_id,manager.career_order "

        print("persionId1==",persionId1)
        cursor.execute(sql)
        managerResults1 = cursor.fetchall()
        fields = cursor.description
        if len(managerResults1)==0:
           print("persionId1=="+persionId1+"===>dc_manager中无此条数据")
           continue
        else:
            for index in range(len(managerResults1)):
                context_type = managerResults1[index].get(u"context_type")
                context_id = managerResults1[index].get(u"context_id")
                #context_type == 2 代表机构，context_type == 3 代表LP，context_type == 5 代表基金，
                if context_type == 2:
                   contextsql = "SELECT org.name as fullName FROM dc_organization org WHERE id='%s'"%context_id
                elif context_type == 3:
                   contextsql = "SELECT lp.name as fullName FROM dc_lp lp WHERE id='%s'"%context_id
                elif context_type == 5:
                   contextsql = "SELECT fund.cn_name as fullName FROM dc_fund fund WHERE id='%s'"%context_id
                else:
                    break
                cursor.execute(contextsql)
                contextResults1 = cursor.fetchall()
                if len(contextResults1)>0:
                    company_full_name1 = contextResults1[0].get(u"fullName")
                    title1 = managerResults1[index].get(u"title")
                    searchCvs2TblPersionManager(managerResults1[index],fields,persionId2,company_full_name1,title1)

    db.close()


def searchCvs2TblPersionManager(managerResults1,fields,persionId2,company_full_name1,title1):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "cms_plus", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()
    career_order = managerResults1.get(u"person_order")
    # sql = "select person_id,person_name,business_id,business_type,title,person_order,level AS beizhu from tbl_com_manager WHERE person_id='%s' ORDER BY person_id,person_order "%persionId2
    sql = "select manager.person_id AS id, manager.person_name AS personName, manager.business_id AS context_id," \
          "manager.business_type AS context_type, manager.title ,manager.person_order, company.full_name," \
          " manager.level AS beizhu " \
          "from" \
          " tbl_com_manager manager LEFT JOIN tbl_company company ON manager.company_id = company.id " \
          "WHERE manager.person_id='%s' AND company.full_name='%s' ORDER BY manager.person_id,manager.person_order"%(persionId2,company_full_name1)

    cursor.execute(sql)
    tblComManagerResults2 = cursor.fetchall()

    if len(tblComManagerResults2)==0:
        print("persionId1=="+str(persionId2)+"===>tbl_com_manager中无此条数据")
        managerResults1.update(beizhu=u'cvs2tbl_com_manager中无符合条件数据,此为cvs1中dc_manager数据')
        writeExcel(managerResults1,fields)
    else:
        company_id = tblComManagerResults2[0].get(u"company_id")
        person_order = tblComManagerResults2[0].get(u"person_order")
        #获取tbl_com_manager中company_id对应的全称
        # companySql = "SELECT full_name FROM tbl_company company WHERE id='%s'"%company_id
        # cursor.execute(companySql)
        # companyResults2 = cursor.fetchall()
        company_full_name2 = tblComManagerResults2[0].get(u"full_name")
        title2 = tblComManagerResults2[0].get(u"title")
        flag = (company_full_name1 == company_full_name2 and title1 == title2 and career_order == person_order)
        if flag != True:
           managerResults1.update(beizhu=u'cvs1和2中tbl_com_manager 任职(在职)公司全称\职位\排序 不完全匹配，此为cvs1中数据')
           managerResults1.update(full_name=company_full_name1)
           writeExcel(managerResults1,fields)
           tblComManagerResults2[0].update(beizhu=u'cvs1和2中tbl_com_manager 任职(在职)公司全称\职位\排序 不完全匹配，此为cvs2中数据')
           writeExcel(tblComManagerResults2[0],fields)

    db.close()

def writeExcel(results,fields):

    fileName = 'dataViewPersonDetailManger.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            # print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
